<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
use PHPExcel_Cell_DataType;
class Tongjixs extends Common
{
   //所属活动进度报表
    public function jinzhan($page=1,$list=10){
         $where="1=1 ";
        $request = Request::instance();
        $data = $request->post();
        $feiid=db("cg_fifeiyongkemu")->where("title like '%陈列%'")->value("id");
        if($feiid){
            $arr=db("cg_feiyongkemu3")->where("fifeiyongkemu<>$feiid")->field("id")->select();
            $tmparr=[];
            for($i=0;$i<count($arr);$i++){
                $tmparr[]=$arr[$i]["id"];
            }
            if($tmparr){
                $tmparr=implode(',',$tmparr);
                $where.="and td.feetype in ($tmparr) ";
            }else{
                //无费用科目
                $where.="and 1<>1 ";
            }
        }
        
      
        $having="count(t.id)>0 ";
        $ishave=0;
        if($data){
            if(trim($data["sn"])){
                $no=trim($data["sn"]);
                $where.="and t.sn='{$no}' ";
            }
            if(trim($data["title"])){
                $title=trim($data["title"]);
                $where.="and t.title='{$title}' ";
            }
            if($data["start"]){
                $ishave=1;
                $start=strtotime($data["start"]);
                $having.="and max(end)>=$start ";
            }
            if($data["end"]){
                $ishave=1;
                $end=strtotime($data["end"]);
                $having.="and min(start)<=$end ";
            }
        }else{
            $data=["sn"=>"","title"=>"","start"=>"","end"=>""];
        }
        if($ishave){
             $sum=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->having($having)
            ->count();
        }else{
            $sum=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")
            ->count();
        }
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        if($ishave){
             $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->having($having)->order("t.id")->limit($start,$list)
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }else{
            $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->order("t.id")->limit($start,$list)
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }
       
       for($i=0;$i<count($apply);$i++){
            $apply[$i]["start"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->min("start");
            $apply[$i]["end"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->max("end");
            $net=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]} and net_id is not null")->group("net_id")->count();
            if($net){
                $apply[$i]["net"]=$net;
            }else{
                $apply[$i]["net"]=0;
            }
            if($apply[$i]["type_id"]==1){
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.tm_id=p.id")->group("p.id")
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }else{
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.product=p.id")->group("p.id") 
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }
            //print_r($apply[$i]["pro"]);
            $apply[$i]["yjsjtrfy"]=round(db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("yjsjtrfy"),2);
            $fb=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijitourufeiyong");
            if($fb){
                $apply[$i]["sjtrfy"]=round($fb,2);
            }else{
                $apply[$i]["sjtrfy"]=0;
            }
            $apply[$i]["syl"]=round($apply[$i]["sjtrfy"]/$apply[$i]["yjsjtrfy"]*100,2);
            $xl=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijixiaoliang");
            if($xl){
                $apply[$i]["xiaoliang"]=round($xl,2);
            }else{
                $apply[$i]["xiaoliang"]='-';
            }
       }
        $this->assign("apply",$apply);
        $this->assign("data",$data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        return $this->fetch();
    }
    public function jinzhandc(){
        $where="1=1 ";
        $feiid=db("cg_fifeiyongkemu")->where("title like '%陈列%'")->value("id");
        if($feiid){
            $arr=db("cg_feiyongkemu3")->where("fifeiyongkemu<>$feiid")->field("id")->select();
            $tmparr=[];
            for($i=0;$i<count($arr);$i++){
                $tmparr[]=$arr[$i]["id"];
            }
            if($tmparr){
                $tmparr=implode(',',$tmparr);
                $where.="and td.feetype in ($tmparr) ";
            }else{
                //无费用科目
                $where.="and 1<>1 ";
            }
        }
        
      
        $having="count(t.id)>0 ";
        $ishave=0;
        if($ishave){
             $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->having($having)->order("t.id")
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }else{
            $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->order("t.id")
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }
       
       for($i=0;$i<count($apply);$i++){
            $apply[$i]["start"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->min("start");
            $apply[$i]["end"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->max("end");
            $net=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]} and net_id is not null")->group("net_id")->count();
            if($net){
                $apply[$i]["net"]=$net;
            }else{
                $apply[$i]["net"]=0;
            }
            if($apply[$i]["type_id"]==1){
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.tm_id=p.id")->group("p.id")
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }else{
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.product=p.id")->group("p.id") 
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }
            //print_r($apply[$i]["pro"]);
            $apply[$i]["yjsjtrfy"]=round(db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("yjsjtrfy"),2);
            $fb=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijitourufeiyong");
            if($fb){
                $apply[$i]["sjtrfy"]=round($fb,2);
            }else{
                $apply[$i]["sjtrfy"]=0;
            }
            $apply[$i]["syl"]=round($apply[$i]["sjtrfy"]/$apply[$i]["yjsjtrfy"]*100,2);
            $xl=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijixiaoliang");
            if($xl){
                $apply[$i]["xiaoliang"]=round($xl,2);
            }else{
                $apply[$i]["xiaoliang"]='-';
            }
       }
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("促销活动执行报表(网点订单类)");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:K1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "促销活动执行报表(网点订单类)");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:K2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"活动编号");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"活动主题");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"活动开始时间");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"活动结束时间");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"执行网点数");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"产品名称");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"预计费用额(元)");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"实际投入费用额(元)");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"费用使用率");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"销量汇总(元)");

        $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["sn"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,date("Y-m-d",$apply[$i]["start"]),PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,date("Y-m-d",$apply[$i]["end"]),PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["net"],PHPExcel_Cell_DataType::TYPE_STRING);
            $arr=[];
            foreach ($apply[$i]["pro"] as $key => $v) {
                # code...
                $arr[]=$v["pro_name"];
            }
            $str=implode(',',$arr);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$str,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["yjsjtrfy"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["sjtrfy"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["syl"]."%",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["xiaoliang"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
          $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:K'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
           
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    public function jinzhan2dc(){
         $where="1=1 ";
        $feiid=db("cg_fifeiyongkemu")->where("title like '%陈列%'")->value("id");
        if($feiid){
            $arr=db("cg_feiyongkemu3")->where("fifeiyongkemu=$feiid")->field("id")->select();
            $tmparr=[];
            for($i=0;$i<count($arr);$i++){
                $tmparr[]=$arr[$i]["id"];
            }
            if($tmparr){
                $tmparr=implode(',',$tmparr);
                $where.="and td.feetype in ($tmparr) ";
            }else{
                //无费用科目
                $where.="and 1<>1 ";
            }
        }else{
             //无费用科目
            $where.="and 1<>1 ";
        }
        
      
        $having="count(t.id)>0 ";
        $ishave=0;
         if($ishave){
             $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->having($having)->order("t.id")
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }else{
            $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->order("t.id")
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }
       
       for($i=0;$i<count($apply);$i++){
            $apply[$i]["start"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->min("start");
            $apply[$i]["end"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->max("end");
            $net=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]} and net_id is not null")->group("net_id")->count();
            if($net){
                $apply[$i]["net"]=$net;
            }else{
                $apply[$i]["net"]=0;
            }
            if($apply[$i]["type_id"]==1){
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.tm_id=p.id")->group("p.id")
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }else{
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.product=p.id")->group("p.id") 
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }
            //print_r($apply[$i]["pro"]);
            $apply[$i]["yjsjtrfy"]=round(db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("yjsjtrfy"),2);
            $fb=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijitourufeiyong");
            if($fb){
                $apply[$i]["sjtrfy"]=round($fb,2);
            }else{
                $apply[$i]["sjtrfy"]=0;
            }
            $apply[$i]["syl"]=round($apply[$i]["sjtrfy"]/$apply[$i]["yjsjtrfy"]*100,2);
            $xl=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijixiaoliang");
            if($xl){
                $apply[$i]["xiaoliang"]=round($xl,2);
            }else{
                $apply[$i]["xiaoliang"]='-';
            }
       }
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("促销活动执行报表(网点陈列类)");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:K1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "促销活动执行报表(网点陈列类)");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:K2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:K3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"活动编号");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"活动主题");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"活动开始时间");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"活动结束时间");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"执行网点数");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"产品名称");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"预计费用额(元)");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"实际投入费用额(元)");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"费用使用率");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"销量汇总(元)");

        $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["sn"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,date("Y-m-d",$apply[$i]["start"]),PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,date("Y-m-d",$apply[$i]["end"]),PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["net"],PHPExcel_Cell_DataType::TYPE_STRING);
            $arr=[];
            foreach ($apply[$i]["pro"] as $key => $v) {
                # code...
                $arr[]=$v["pro_name"];
            }
            $str=implode(',',$arr);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$str,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["yjsjtrfy"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["sjtrfy"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["syl"]."%",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["xiaoliang"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
          $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:R'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
           
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //所属促销活动执行报表（网点陈列类）
    public function jinzhan2($page=1,$list=10){
          //获取所属经销商
        $where="1=1 ";
        $request = Request::instance();
        $data = $request->post();
        $feiid=db("cg_fifeiyongkemu")->where("title like '%陈列%'")->value("id");
        if($feiid){
            $arr=db("cg_feiyongkemu3")->where("fifeiyongkemu=$feiid")->field("id")->select();
            $tmparr=[];
            for($i=0;$i<count($arr);$i++){
                $tmparr[]=$arr[$i]["id"];
            }
            if($tmparr){
                $tmparr=implode(',',$tmparr);
                $where.="and td.feetype in ($tmparr) ";
            }else{
                //无费用科目
                $where.="and 1<>1 ";
            }
        }else{
             //无费用科目
            $where.="and 1<>1 ";
        }
        
      
        $having="count(t.id)>0 ";
        $ishave=0;
        if($data){
            if(trim($data["sn"])){
                $no=trim($data["sn"]);
                $where.="and t.sn='{$no}' ";
            }
            if(trim($data["title"])){
                $title=trim($data["title"]);
                $where.="and t.title='{$title}' ";
            }
            if($data["start"]){
                $ishave=1;
                $start=strtotime($data["start"]);
                $having.="and max(end)>=$start ";
            }
            if($data["end"]){
                $ishave=1;
                $end=strtotime($data["end"]);
                $having.="and min(start)<=$end ";
            }
        }else{
            $data=["sn"=>"","title"=>"","start"=>"","end"=>""];
        }
        if($ishave){
             $sum=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->having($having)
            ->count();
        }else{
            $sum=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")
            ->count();
        }
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        if($ishave){
             $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->having($having)->order("t.id")->limit($start,$list)
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }else{
            $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->group("t.id")->where($where." and t.status=7 and (t.status2=0 or t.status2 is null)")->order("t.id")->limit($start,$list)
            ->field("t.sn,t.id,t.type_id,t.title")
            ->select();
        }
       
       for($i=0;$i<count($apply);$i++){
            $apply[$i]["start"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->min("start");
            $apply[$i]["end"]=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->max("end");
            $net=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]} and net_id is not null")->group("net_id")->count();
            if($net){
                $apply[$i]["net"]=$net;
            }else{
                $apply[$i]["net"]=0;
            }
            if($apply[$i]["type_id"]==1){
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.tm_id=p.id")->group("p.id")
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }else{
                $apply[$i]["pro"]=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("pro_products p","td.product=p.id")->group("p.id") 
                ->where("t.id={$apply[$i]["id"]}")->field("p.pro_name")->select();
            }
            //print_r($apply[$i]["pro"]);
            $apply[$i]["yjsjtrfy"]=round(db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("yjsjtrfy"),2);
            $fb=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijitourufeiyong");
            if($fb){
                $apply[$i]["sjtrfy"]=round($fb,2);
            }else{
                $apply[$i]["sjtrfy"]=0;
            }
            $apply[$i]["syl"]=round($apply[$i]["sjtrfy"]/$apply[$i]["yjsjtrfy"]*100,2);
            $xl=db("cg_ti_list")->where("ti_id={$apply[$i]["id"]}")->sum("fb_shijixiaoliang");
            if($xl){
                $apply[$i]["xiaoliang"]=round($xl,2);
            }else{
                $apply[$i]["xiaoliang"]='-';
            }
       }
        $this->assign("apply",$apply);
        $this->assign("data",$data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        return $this->fetch();
    }
    //销售导出
    public function xiaoshoudc($cid){
        $jxslist=$this->my_own_jxs();
          $where="1=1 ";
            $whereti="1=1 ";
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->order("starttime")->select();
        $nian=db("cg_cainian")->where("id=$cid")->value("title");
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
           
            $where="sdAgency_id in ({$str}) ";
            $whereti="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
            $whereti="1<>1 ";
        }
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->order("starttime")->select();
        $this->assign("kaohe1",$kaohe1);
        //发运
        $fy=[];$zfy=0;
        //ti
        $ti=[];$zti=0;
        for($i=0;$i<count($kaohe1);$i++){

            //发运
            $tmpstart=date("Y-m-d",$kaohe1[$i]["starttime"]);
            $tmpend=date("Y-m-d",$kaohe1[$i]["endtime"]);
            $end=$kaohe1[$i]["endtime"]+3600*24;
            $fye=db("bx_fy")->where($where." and fyDate<='$tmpend' and fyDate>='{$tmpstart}'")->sum("fyZheMoney");
            if($fye){
                $fy[]=round($fye,2);
                $zfy+=round($fye,2);
            }else{
                $fy[]=0;
            }
            //ti
            $tie=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where($whereti." and t.ctime>={$kaohe1[$i]["starttime"]} and t.ctime<{$end} and t.status=7 and (t.status2=0 or t.status2 is null)")->field("td.yjsjtrfy,td.fb_shijitourufeiyong")->select();
            $tmpti=0;
            for($j=0;$j<count($tie);$j++){
                if($tie[$j]["fb_shijitourufeiyong"]){
                    $tmpti+=round($tie[$j]["fb_shijitourufeiyong"],2);
                }else{
                    $tmpti+=round($tie[$j]["yjsjtrfy"],2);
                }
            }
            $ti[]=$tmpti;
            $zti+=$tmpti;
        }
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("{$nian}销售及费用进度");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:F1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$nian}销售及费用进度");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:F2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);

        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:F3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:F3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:F3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:F3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"第一季度");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"第二季度");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"第三季度");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"第四季度");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"合计");
        $h=3;
        $h++;

        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"TI费用",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$ti[0],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$ti[1],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$ti[2],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$ti[3],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$zti,PHPExcel_Cell_DataType::TYPE_STRING);
        $h++;
        
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"发运额",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$fy[0],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$fy[1],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$fy[2],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$fy[3],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$zfy,PHPExcel_Cell_DataType::TYPE_STRING);

         $h++;
        
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"回款额",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);

         $h++;
        
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"零售额",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,0,PHPExcel_Cell_DataType::TYPE_STRING);

        $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:F'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
           
           
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    } 
    //所属销售及费用进度
    public function xiaoshou($cid=null){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        
            $where="1=1 ";
            $whereti="1=1 ";
        
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->order("starttime")->select();
        $this->assign("kaohe1",$kaohe1);
        //发运
        $fy=[];$zfy=0;
        //ti
        $ti=[];$zti=0;
        for($i=0;$i<count($kaohe1);$i++){

            //发运
            $tmpstart=date("Y-m-d",$kaohe1[$i]["starttime"]);
            $tmpend=date("Y-m-d",$kaohe1[$i]["endtime"]);
            $end=$kaohe1[$i]["endtime"]+3600*24;
            $fye=db("bx_fy")->where($where." and fyDate<='$tmpend' and fyDate>='{$tmpstart}'")->sum("fyZheMoney");
            if($fye){
                $fy[]=round($fye,2);
                $zfy+=round($fye,2);
            }else{
                $fy[]=0;
            }
            //ti
            $tie=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where($whereti." and t.ctime>={$kaohe1[$i]["starttime"]} and t.ctime<{$end} and t.status=7 and (t.status2=0 or t.status2 is null)")->field("td.yjsjtrfy,td.fb_shijitourufeiyong")->select();
            $tmpti=0;
            for($j=0;$j<count($tie);$j++){
                if($tie[$j]["fb_shijitourufeiyong"]){
                    $tmpti+=round($tie[$j]["fb_shijitourufeiyong"],2);
                }else{
                    $tmpti+=round($tie[$j]["yjsjtrfy"],2);
                }
            }
            $ti[]=$tmpti;
            $zti+=$tmpti;
        }
        $this->assign("zfy",$zfy);
        $this->assign("fy",$fy);
        $this->assign("ti",$ti);
        $this->assign("zti",$zti);
        $this->assign("kaohec",count($kaohe1)+1);

        return $this->fetch();
    }
    //TI预算使用报表1
    public function yusuan($cid='',$page=1,$list=10){
         $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $request = Request::instance();
        $data = $request->post();
        //print_r($data);
        $where="type=1 and jine<>0 and cainian=$cid ";
        $td=[];
        $group=[];
        $fenqu=[];$sheng=[];$shi=[];$pro=[];
        if($data){
            if($data["customW"]==2){
                //分区
                $td[]="分区";
                $group[]="fenqu";
                $data["sheng"]='';$data["shi"]='';
                if($data["fenqu"]){
                    $data["fenqu"]=implode(',',explode('-',$data["fenqu"]));
                    $fenqu=db("cg_market_zoning")->where("id in ({$data["fenqu"]})")->select();
                    $where.="and fenqu in ({$data["fenqu"]}) ";
                }
            }else if($data["customW"]==3){
                //省份
                $td[]="分区";$td[]="省份";
                $group[]="fenqu";
                $group[]="sheng";
                $data["fenqu"]='';$data["shi"]='';
                if($data["sheng"]){
                    $data["sheng"]=implode(',',explode('-',$data["sheng"]));
                    $sheng=db("cg_market_provice")->where("id in ({$data["sheng"]})")->select();
                    $where.="and sheng in ({$data["sheng"]}) ";
                }
            }else if($data["customW"]==4){
                //省份
                $td[]="分区";$td[]="省份";$td[]="城市";
                 $group[]="fenqu";
                $group[]="sheng";
                $group[]="shi";
                $data["sheng"]='';$data["fenqu"]='';
                if($data["shi"]){
                    $data["shi"]=implode(',',explode('-',$data["shi"]));
                    $shi=db("cg_market_city")->where("id in ({$data["shi"]})")->select();
                    $where.="and shi in ({$data["shi"]}) ";
                }
            }

            if($data["product"]==1){
                $td[]="品牌";
                $group[]="pinpai";
                if($data["brand"]){
                    $data["brand"]=implode(',',explode('-',$data["brand"]));
                    $pro=db("pro_brands")->where("id in ({$data["brand"]})")->select();
                    $where.="and pinpai in ({$data["brand"]}) ";
                }
            }
            if($data["kemu"]==1){
                $td[]="一级费用科目";
                $group[]="yijifeiyong";
            }
            if($data["cainian"]==1){
                $td[]="一级考核期";
                $group[]="yijikaoheqi";
            }else if($data["cainian"]==2){
                $td[]="一级考核期";
                $td[]="二级考核期";
                $group[]="yijikaoheqi";
                $group[]="erjikaoheqi";
            }
        }else{
            $data=["customW"=>1,"product"=>0,"kemu"=>0,"cainian"=>0,"searchKind"=>0,"fenqu"=>'',"sheng"=>"","shi"=>"","brand"=>""];
        }
        $this->assign("data",$data);
        $this->assign("fenqu",$fenqu);
        $this->assign("sheng",$sheng);
        $this->assign("shi",$shi);
        $this->assign("pro",$pro);
        $this->assign("td",$td);
        //查询预算
        if($group){
            $gp=implode(',',$group);
            $sum=db("yusuan")->where($where)->group($gp)->count();
            $maxpage=ceil($sum/$list);
            $page=$page<$maxpage?$page:$maxpage;
            $page=$page>1?$page:1;

            $start=($page-1)*$list;
            $arr=db("yusuan")->where($where)->group($gp)->limit($start,$list)->field("$gp,sum(jine) as jine")->select();
            //print_r($arr);exit;
            
            foreach ($arr as $k => $v) {
                # code...
                $wheres="type=1 and jine<>0 and cainian=$cid ";
                foreach ($group as $kg => $vg) {
                    # code...
                    if($vg=="fenqu"){
                        if($v["fenqu"])
                            $wheres.="and fenqu={$v["fenqu"]} ";
                        else
                            $wheres.="and fenqu is null";
                    }
                    if($vg=="sheng"){
                        if($v["sheng"])
                            $wheres.="and sheng={$v["sheng"]} ";
                        else
                            $wheres.="and sheng is null ";
                    }
                    if($vg=="shi"){
                        if($v["shi"])
                            $wheres.="and shi={$v["shi"]} ";
                        else
                            $wheres.="and shi is null ";
                    }
                    if($vg=="pinpai"){
                        if($v["pinpai"])
                            $wheres.="and pinpai={$v["pinpai"]} ";
                        else
                            $wheres.="and pinpai is null ";
                    }
                    if($vg=="yijifeiyong"){
                        if($v["yijifeiyong"])
                            $wheres.="and yijifeiyong={$v["yijifeiyong"]} ";
                        else
                            $wheres.="and yijifeiyong is null ";
                    }
                    if($vg=="yijikaoheqi"){
                        if($v["yijikaoheqi"])
                            $wheres.="and yijikaoheqi={$v["yijikaoheqi"]} ";
                        else
                            $wheres.="and yijikaoheqi is null ";
                    }
                        
                    if($vg=="erjikaoheqi"){
                        if($v["erjikaoheqi"])
                            $wheres.="and erjikaoheqi={$v["erjikaoheqi"]} ";
                        else
                            $wheres.="and erjikaoheqi is null ";
                    }
                }
                //echo $wheres."<br>";
                $tmp=db("yusuan")->where($wheres)->field("id")->select();
                $arr[$k]["ids"]=[];
                foreach ($tmp as $kt => $vt) {
                    # code...
                    $arr[$k]["ids"][]=$vt["id"];
                }
                $arr[$k]["ids"]=implode(',',$arr[$k]["ids"]);
            }
        }else{
            $sum=1;
            $maxpage=ceil($sum/$list);
            $page=$page<$maxpage?$page:$maxpage;
            $page=$page>1?$page:1;

            $start=($page-1)*$list;
            $arr=db("yusuan")->where($where)->field("sum(jine) as jine")->select();
            $tmp=db("yusuan")->where($where)->field("id")->select();
                $arr[0]["ids"]=[];
                foreach ($tmp as $kt => $vt) {
                    # code...
                    $arr[0]["ids"][]=$vt["id"];
                }
                $arr[0]["ids"]=implode(',',$arr[0]["ids"]);
        }
        for ($i=0; $i <count($arr) ; $i++) { 
            # code...
            $pifu=db("yusuan_detail")->where("yid in ({$arr[$i]["ids"]}) and type=2")->sum("jine");
            if($pifu)
                $arr[$i]["pifu"]=round($pifu,2);
            else
                $arr[$i]["pifu"]=0;
            $arr[$i]["weiyong"]=$arr[$i]["jine"]-$arr[$i]["pifu"];
            $sqz=db("yusuan_detail")->where("yid in ({$arr[$i]["ids"]}) and type=1")->sum("jine");
            if($sqz)
                $arr[$i]["sqz"]=round($sqz,2);
            else
                $arr[$i]["sqz"]=0;
            if($data["cainian"]==0){
                //cainian
                $tmpc=db("cg_cainian")->where("id=$cid")->find();
                $arr[$i]["time"]=date("Y-m-d",$tmpc["starttime"])."至".date("Y-m-d",$tmpc["endtime"]);
            }else if($data["cainian"]==1){
                $tmpc=db("cg_kaoheqi1")->where("id={$arr[$i]["yijikaoheqi"]}")->find();
                $arr[$i]["time"]=date("Y-m-d",$tmpc["starttime"])."至".date("Y-m-d",$tmpc["endtime"]);
            }else{
                if($arr[$i]["erjikaoheqi"]){
                    $tmpc=db("cg_kaoheqi2")->where("id={$arr[$i]["erjikaoheqi"]}")->find();
                    $arr[$i]["time"]=date("Y-m-d",$tmpc["starttime"])."至".date("Y-m-d",$tmpc["endtime"]);
                }else{
                    $tmpc=db("cg_kaoheqi1")->where("id={$arr[$i]["yijikaoheqi"]}")->find();
                    $arr[$i]["time"]=date("Y-m-d",$tmpc["starttime"])."至".date("Y-m-d",$tmpc["endtime"]);
                }
            }
            $index=$i+1;
            $arr[$i]["tr"]="<tr><td>{$index}</td>";
            foreach ($group as $k => $v) {
                # code...
                if($v=="fenqu"){
                    if($arr[$i]["fenqu"]){
                        $tmpfenqu=db("cg_market_zoning")->where(["id"=>$arr[$i]["fenqu"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmpfenqu}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="sheng"){
                    if($arr[$i]["sheng"]){
                        $tmpsheng=db("cg_market_provice")->where(["id"=>$arr[$i]["sheng"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmpsheng}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="shi"){
                    if($arr[$i]["shi"]){
                        $tmpshi=db("cg_market_city")->where(["id"=>$arr[$i]["shi"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmpshi}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="pinpai"){
                    if($arr[$i]["pinpai"]){
                        $tmppinpai=db("pro_brands")->where(["id"=>$arr[$i]["pinpai"]])->value("brand_name");
                        $arr[$i]["tr"].="<td>{$tmppinpai}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="yijifeiyong"){
                    if($arr[$i]["yijifeiyong"]){
                        $tmpyijifeiyong=db("cg_feiyongkemu1")->where(["id"=>$arr[$i]["yijifeiyong"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmpyijifeiyong}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="yijikaoheqi"){
                    if($arr[$i]["yijikaoheqi"]){
                        $tmpyijikaoheqi=db("cg_kaoheqi1")->where(["id"=>$arr[$i]["yijikaoheqi"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmpyijikaoheqi}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="erjikaoheqi"){
                    if($arr[$i]["erjikaoheqi"]){
                        $tmperjikaoheqi=db("cg_kaoheqi2")->where(["id"=>$arr[$i]["erjikaoheqi"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmperjikaoheqi}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
            }

            $arr[$i]["tr"].="<td class='ObjectNum text-right jisuan1'>{$arr[$i]["jine"]}</td>";
            $arr[$i]["tr"].="<td class='ObjectNum text-right jisuan2'>{$arr[$i]["pifu"]}</td>";
            $arr[$i]["tr"].="<td class='ObjectNum text-right jisuan3'>{$arr[$i]["weiyong"]}</td>";
            $arr[$i]["tr"].="<td class='ObjectNum text-right jisuan4'>{$arr[$i]["sqz"]}</td>";
            $arr[$i]["tr"].="<td>{$arr[$i]["time"]}</td>";
            if($arr[$i]["pifu"]>=$arr[$i]["jine"]){
                 $arr[$i]["tr"].="<td><div class='Redlay'>红灯</div></td>";
            }else if($arr[$i]["pifu"]+$arr[$i]["sqz"]>=$arr[$i]["jine"]){
                $arr[$i]["tr"].="<td><div class='Yellowlay'>黄灯</div></td>";
            }else{
                $arr[$i]["tr"].="<td><div class='Greenlay'>绿灯</div></td>";
            }
            $arr[$i]["tr"].="</tr>";
        }
        $this->assign("arr",$arr);
         $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
       // print_r($arr);
        return $this->fetch();
    }
    public function yusuan2($cid=0,$k1id=0,$k2id=0){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->order("starttime")->select();
        $this->assign("kaohe1",$kaohe1);
        $this->assign("k1id",$k1id);
        if($k1id){
            $kaohe2=db("cg_kaoheqi2")->where(["pid"=>$k1id])->order("starttime")->select();
        }else{
            $kaohe2=[];
        }
        $this->assign("kaohe2",$kaohe2);
        $this->assign("k2id",$k2id);
        $fenqu=[];$sheng=[];$shi=[];$jxs=[];$yijiarr=[];$erjiarr=[];$sanjiarr=[];$kemuarr=[];
        $request = Request::instance();

        $data = $request->post();
        if($data){

            if(isset($data["kind"])){
                $kindarr=$data["kind"];
            }else{
                $kindarr=[];
            }
             //科目纬度
            if($data["kemu"]==2){
                //二级费用科目
                if($data["erjifeiyong"]){
                    $kemu2=implode(',',explode('-',$data["erjifeiyong"]));
                    $kemuarr=db("cg_feiyongkemu2")->where("id in ($kemu2)")->select();
                    $erjiarr=db("cg_feiyongkemu2")->where("id in ($kemu2)")->select();
                }else{
                    $kemuarr=db("cg_feiyongkemu2")->select();
                }
                $data["yijifeiyong"]="";
                $data["sanjifeiyong"]="";
            }else if($data["kemu"]==3){
               
                //三级费用科目
                if($data["sanjifeiyong"]){
                    $kemu3=implode(',',explode('-',$data["sanjifeiyong"]));
                   
                    $kemuarr=db("cg_feiyongkemu3")->where("id in ($kemu3)")->select();
                    $sanjiarr=db("cg_feiyongkemu3")->where("id in ($kemu3)")->select();
                }else{
                    $kemuarr=db("cg_feiyongkemu3")->select();
                }
                $data["erjifeiyong"]="";
                $data["sanjifeiyong"]="";
            }else{
                //一级费用科目
                if($data["yijifeiyong"]){
                    $kemu1=implode(',',explode('-',$data["yijifeiyong"]));
                    $kemuarr=db("cg_feiyongkemu1")->where("id in ($kemu1)")->select();
                    $yijiarr=db("cg_feiyongkemu1")->where("id in ($kemu1)")->select();
                }else{
                    $kemuarr=db("cg_feiyongkemu1")->select();
                }
                $data["erjifeiyong"]="";
                $data["sanjifeiyong"]="";

            }
            if($data["customW"]==1){
                //分区
                if($data["fenqu"]){
                    $fq=implode(',',explode('-',$data["fenqu"]));
                    $fenqu=db("cg_market_zoning")->where("id in ($fq)")->field("id,title")->select();
                    $diqu=db("cg_market_zoning")->where("id in ($fq)")->field("id,title")->select();
                }else{
                    $diqu=db("cg_market_zoning")->where("isdel=0")->field("id,title")->select();
                }
                $data["sheng"]="";$data["shi"]="";$data["jxs"]="";
            }else if($data["customW"]==2){
                //省
                if($data["sheng"]){
                    $fq=implode(',',explode('-',$data["sheng"]));
                    $sheng=db("cg_market_provice")->where("id in ($fq)")->field("id,title")->select();
                    $diqu=db("cg_market_provice")->where("id in ($fq)")->field("id,title")->select();
                }else{
                    $diqu=db("cg_market_provice")->where("isdel=0")->field("id,title")->select();
                }
                $data["fenqu"]="";$data["shi"]="";$data["jxs"]="";
            }else if($data["customW"]==3){
                //市
                if($data["shi"]){
                    $fq=implode(',',explode('-',$data["shi"]));
                    $shi=db("cg_market_city")->where("id in ($fq)")->field("id,title")->select();
                    $diqu=db("cg_market_city")->where("id in ($fq)")->field("id,title")->select();
                }else{
                    $diqu=db("cg_market_city")->where("isdel=0")->field("id,title")->select();
                }
                $data["fenqu"]="";$data["sheng"]="";$data["jxs"]="";
            }else{
                //经销商
                if($data["jxs"]){
                    $fq=implode(',',explode('-',$data["jxs"]));
                    //echo $fq;
                    $jxs=db("cg_jxs")->where("id in ($fq)")->field("id,title")->select();
                    $diqu=db("cg_jxs")->where("id in ($fq)")->field("id,title")->select();
                }else{
                    $diqu=db("cg_jxs")->where("isdel=0")->field("id,title")->select();
                }
                $data["fenqu"]="";$data["shi"]="";$data["sheng"]="";
            }
        }else{
            $kindarr=[1,2,3,4];
            $kemuarr=db("cg_feiyongkemu1")->select();
            $diqu=db("cg_market_zoning")->where("isdel=0")->field("id,title")->select();
            $data=["customW"=>1,"fenqu"=>'',"sheng"=>"","shi"=>"","jxs"=>"","kemu"=>1,"yijifeiyong"=>"","erjifeiyong"=>"","sanjifeiyong"=>"","searchKind"=>0];
        }
        
        if($k2id){
           // echo $k2id;
            $kaohedian=db("cg_kaoheqi2")->where(["id"=>$k2id])->find();
        }else if($k1id){
            //echo $k1id;
            $kaohedian=db("cg_kaoheqi1")->where(["id"=>$k1id])->find();
        }else{
            //echo $cid;
            $kaohedian=db("cg_cainian")->where(["id"=>$cid])->find();
        }
        $all=[];

        if($kindarr){
            for($i=0;$i<count($kindarr);$i++){
                $all["lan"][$i]=[];
                if($kindarr[$i]==1){
                    $all["lan"][$i]["titles"]="预算";
                }
                if($kindarr[$i]==2){
                    $all["lan"][$i]["titles"]="批复金额";
                }
                if($kindarr[$i]==3){
                    $all["lan"][$i]["titles"]="申请金额";
                }
                if($kindarr[$i]==4){
                    $all["lan"][$i]["titles"]="预算结余";
                }
                for($j=0;$j<count($kemuarr);$j++){
                    $all["lan"][$i]["jine"][$j]=0;
                }
                $all["lan"][$i]["zjine"]=0;
            }
            for($i=0;$i<count($diqu);$i++){
                    //预算
                $whereys="y.type=1 and y.jine<>0 ";
                if($k2id){
                    $whereys.="and y.erjikaoheqi=$k2id ";
                }else if($k1id){
                    $whereys.="and y.yijikaoheqi=$k1id ";
                }else{
                    $whereys.="and y.cainian=$cid ";
                }
                if($data["customW"]==1){
                    $whereys.="and y.fenqu={$diqu[$i]["id"]} ";
                }else if($data["customW"]==2){
                    $whereys.="and y.sheng={$diqu[$i]["id"]} ";
                }else if($data["customW"]==3){
                    $whereys.="and y.shi={$diqu[$i]["id"]} ";
                }else if($data["customW"]==4){
                    $whereys.="and y.jingxiao={$diqu[$i]["id"]} ";
                }
                $diqu[$i]["lan"]=[];$diqu[$i]["heji"]=[];$diqu[$i]["titles"]=[];
                $diqu[$i]["ys"]=[];$diqu[$i]["zys"]=0; 
                $diqu[$i]["sq"]=[];$diqu[$i]["zsq"]=0; 
                $diqu[$i]["sqz"]=[];$diqu[$i]["zsqz"]=0; 
                $diqu[$i]["wsq"]=[];$diqu[$i]["zwsq"]=0; 
                for($j=0;$j<count($kemuarr);$j++){
                    $whereyskm=$whereys;
                    if($data["kemu"]==1){
                        $whereyskm.="and y.yijifeiyong={$kemuarr[$j]["id"]} ";
                    }else if($data["kemu"]==2){
                        $whereyskm.="and y.erjifeiyong={$kemuarr[$j]["id"]} ";
                    }else if($data["kemu"]==3){
                        $whereyskm.="and y.sanjifeiyong={$kemuarr[$j]["id"]} ";
                    }
                    $ysk=db("yusuan y")->where($whereyskm)->sum("y.jine");
                    if($ysk){
                        $diqu[$i]["ys"][]=round($ysk,2);
                        $diqu[$i]["zys"]+=round($ysk,2);
                    }else{
                        $diqu[$i]["ys"][]=0;
                    }
                    $sqk=db("yusuan y")->join("yusuan_detail yd","yd.yid=y.id")
                    ->where($whereyskm." and yd.type=2")->sum("yd.jine");
                    if($sqk){
                        $diqu[$i]["sq"][]=round($sqk,2);
                        $diqu[$i]["zsq"]+=round($sqk,2);
                    }else{
                        $diqu[$i]["sq"][]=0;
                    }

                    $sqkz=db("yusuan y")->join("yusuan_detail yd","yd.yid=y.id")
                    ->where($whereyskm." and yd.type=1")->sum("yd.jine");
                    if($sqkz){
                        $diqu[$i]["sqz"][]=round($sqkz,2);
                        $diqu[$i]["zsqz"]+=round($sqkz,2);
                    }else{
                        $diqu[$i]["sqz"][]=0;
                    }
                    $diqu[$i]["wsq"][]=round($ysk,2)-round($sqk,2);
                    $diqu[$i]["zwsq"]+=(round($ysk,2)-round($sqk,2));
                }
                if(in_array(1,$kindarr)){
                    $diqu[$i]["lan"][]=$diqu[$i]["ys"];
                    $diqu[$i]["heji"][]=$diqu[$i]["zys"];
                    $diqu[$i]["titles"][]="预算";
                }
                if(in_array(2,$kindarr)){
                    $diqu[$i]["lan"][]=$diqu[$i]["sq"];
                    $diqu[$i]["heji"][]=$diqu[$i]["zsq"];
                    $diqu[$i]["titles"][]="批复金额";
                }
                if(in_array(3,$kindarr)){
                    $diqu[$i]["lan"][]=$diqu[$i]["sqz"];
                    $diqu[$i]["heji"][]=$diqu[$i]["zsqz"];
                    $diqu[$i]["titles"][]="申请金额";
                }
                if(in_array(4,$kindarr)){
                    $diqu[$i]["lan"][]=$diqu[$i]["wsq"];
                    $diqu[$i]["heji"][]=$diqu[$i]["zwsq"];
                    $diqu[$i]["titles"][]="预算结余";
                }
            }
            for($i=0;$i<count($all["lan"]);$i++){
                for($j=0;$j<count($kemuarr);$j++){
                    foreach ($diqu as $k => $v) {
                        # code...
                        $all["lan"][$i]["jine"][$j]+=$v["lan"][$i][$j];
                        $all["lan"][$i]["zjine"]+=$v["lan"][$i][$j];
                        //echo $all["lan"][$i]["jine"][$j]."<br>";
                    }
                }

            }
        }
        
        $this->assign("fenqu",$fenqu);
        $this->assign("sheng",$sheng);
        $this->assign("shi",$shi);
        $this->assign("jxs",$jxs);
        $this->assign("data",$data);
        $this->assign("yijiarr",$yijiarr);
        $this->assign("erjiarr",$erjiarr);
        $this->assign("sanjiarr",$sanjiarr);
        $this->assign("kindarr",$kindarr);
        $this->assign("kaohedian",$kaohedian);
        $this->assign("kemuarr",$kemuarr);
        $this->assign("diqu",$diqu);
        $this->assign("all",$all);
        return $this->fetch();
    }
    public function yusuan3($cid='',$kid=1){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $this->assign("kid",$kid);
        $fenqu=[];$sheng=[];$shi=[];$jxs=[];$yijiarr=[];$erjiarr=[];$sanjiarr=[];$kemuarr=[];
        $request = Request::instance();

        $data = $request->post();
        if($data){

            if(isset($data["kind"])){
                $kindarr=$data["kind"];
            }else{
                $kindarr=[];
            }
             //科目纬度
            if($data["kemu"]==2){
                //二级费用科目
                if($data["erjifeiyong"]){
                    $kemu2=implode(',',explode('-',$data["erjifeiyong"]));
                    $kemuarr=db("cg_feiyongkemu2")->where("id in ($kemu2)")->select();
                    $erjiarr=db("cg_feiyongkemu2")->where("id in ($kemu2)")->select();
                }else{
                    $kemuarr=db("cg_feiyongkemu2")->select();
                }
                $data["yijifeiyong"]="";
                $data["sanjifeiyong"]="";
            }else if($data["kemu"]==3){
               
                //三级费用科目
                if($data["sanjifeiyong"]){
                    $kemu3=implode(',',explode('-',$data["sanjifeiyong"]));
                   
                    $kemuarr=db("cg_feiyongkemu3")->where("id in ($kemu3)")->select();
                    $sanjiarr=db("cg_feiyongkemu3")->where("id in ($kemu3)")->select();
                }else{
                    $kemuarr=db("cg_feiyongkemu3")->where("esuseway like '%\"6\"%'")->select();
                }
                $data["erjifeiyong"]="";
                $data["sanjifeiyong"]="";
            }else{
                //一级费用科目
                if($data["yijifeiyong"]){
                    $kemu1=implode(',',explode('-',$data["yijifeiyong"]));
                    $kemuarr=db("cg_feiyongkemu1")->where("id in ($kemu1)")->select();
                    $yijiarr=db("cg_feiyongkemu1")->where("id in ($kemu1)")->select();
                }else{
                    $kemuarr=db("cg_feiyongkemu1")->select();
                }
                $data["erjifeiyong"]="";
                $data["sanjifeiyong"]="";

            }
            if($data["customW"]==1){
                //分区
                if($data["fenqu"]){
                    $fq=implode(',',explode('-',$data["fenqu"]));
                    $fenqu=db("cg_market_zoning")->where("id in ($fq)")->field("id,title")->select();
                    $diqu=db("cg_market_zoning")->where("id in ($fq)")->field("id,title")->select();
                }else{
                    $diqu=db("cg_market_zoning")->where("isdel=0")->field("id,title")->select();
                }
                $data["sheng"]="";$data["shi"]="";$data["jxs"]="";
            }else if($data["customW"]==2){
                //省
                if($data["sheng"]){
                    $fq=implode(',',explode('-',$data["sheng"]));
                    $sheng=db("cg_market_provice")->where("id in ($fq)")->field("id,title")->select();
                    $diqu=db("cg_market_provice")->where("id in ($fq)")->field("id,title")->select();
                }else{
                    $diqu=db("cg_market_provice")->where("isdel=0")->field("id,title")->select();
                }
                $data["fenqu"]="";$data["shi"]="";$data["jxs"]="";
            }else if($data["customW"]==3){
                //市
                if($data["shi"]){
                    $fq=implode(',',explode('-',$data["shi"]));
                    $shi=db("cg_market_city")->where("id in ($fq)")->field("id,title")->select();
                    $diqu=db("cg_market_city")->where("id in ($fq)")->field("id,title")->select();
                }else{
                    $diqu=db("cg_market_city")->where("isdel=0")->field("id,title")->select();
                }
                $data["fenqu"]="";$data["sheng"]="";$data["jxs"]="";
            }else{
                //经销商
                if($data["jxs"]){
                    $fq=implode(',',explode('-',$data["jxs"]));
                    $jxs=db("cg_jxs")->where("id in ($fq)")->field("id,title")->select();
                    $diqu=db("cg_market_city")->where("id in ($fq)")->field("id,title")->select();
                }else{
                    $diqu=db("cg_jxs")->where("isdel=0")->field("id,title")->select();
                }
                $data["fenqu"]="";$data["shi"]="";$data["sheng"]="";
            }
        }else{
            $kindarr=[1,2,3];
            $kemuarr=db("cg_feiyongkemu1")->select();
            $diqu=db("cg_market_zoning")->where("isdel=0")->field("id,title")->select();
            $data=["customW"=>1,"fenqu"=>'',"sheng"=>"","shi"=>"","jxs"=>"","kemu"=>1,"yijifeiyong"=>"","erjifeiyong"=>"","sanjifeiyong"=>"","searchKind"=>0];
        }
        if($kid==1){
            $kaohedian=db("cg_kaoheqi1")->where(["cid"=>$cid])->order("starttime")->select();
        }else{
            $kaohedian=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->where(["k1.cid"=>$cid])->order("k2.starttime")->field("k2.*")->select();
        }
        if($kindarr){
            for($i=0;$i<count($diqu);$i++){
                $diqu[$i]["ke"]=count($kindarr)*count($kemuarr);
                $whereys="y.type=1 and y.jine<>0 ";
                    
                if($data["customW"]==1){
                    $whereys.="and y.fenqu={$diqu[$i]["id"]} ";
                }else if($data["customW"]==2){
                    $whereys.="and y.sheng={$diqu[$i]["id"]} ";
                }else if($data["customW"]==3){
                    $whereys.="and y.shi={$diqu[$i]["id"]} ";
                }else if($data["customW"]==4){
                    $whereys.="and y.jingxiao={$diqu[$i]["id"]} ";
                }

                for($j=0;$j<count($kaohedian);$j++){
                    if($kid=1){
                       
                    }
                }
            }
        }
        $this->assign("diqu",$diqu);
        $this->assign("fenqu",$fenqu);
        $this->assign("sheng",$sheng);
        $this->assign("shi",$shi);
        $this->assign("jxs",$jxs);
        $this->assign("data",$data);
        $this->assign("yijiarr",$yijiarr);
        $this->assign("erjiarr",$erjiarr);
        $this->assign("sanjiarr",$sanjiarr);
        $this->assign("kindarr",$kindarr);
        $this->assign("kaohedian",$kaohedian);
        $this->assign("kemuarr",$kemuarr);
        return $this->fetch();
    }
    //费用科目3数据
    public function feiyong3json(){
        
        $where = [];
       
        $data = Db::table('cg_feiyongkemu3')->field('title,id')->where($where)->select();
//        echo Db::table('cg_feiyongkemku3')->getLastSql();
        echo json_encode(['message'=>'ok','value'=>$data,'code'=>'200','redirect'=>'']);
    }
}